<cfcomponent output="false">
	<cfset $ = application.helpers />
	
	<!--- --->
	
	<cffunction name="getUser" access="public" output="true" returntype="query">
		<cfargument name="email" required="false" default="" />
		<cfargument name="password" required="false" default="" />
		<cfargument name="userID" required="false" default="" />
		<cfargument name="cachedWithin" required="false" default="#createTimespan(0,0,15,0)#" />
		<cfargument name="invalidateCache" required="false" default="true" /> <!--- for now, never cache this query --->
		<cfargument name="order" required="false" default="" />
		
		<cfset var local = {} />
		
		<cfif arguments.invalidateCache>
			<cfset arguments.cachedWithin = createTimespan(0,0,0,0) />
		</cfif>

		<cfquery name="local.getUser" datasource="#$.site.getVar('datasource')#" cachedwithin="#arguments.cachedWithin#">
			select 
			hex(user.id) as id,
			user.first_name,
			user.middle_name,
			user.last_name,
			user.email,
			user.password,
			user.bio,
			user.event_categories,
			user.options,
			hex(user.business_id) as business_id,
			user.refresh_token,
			user.access_token,
			user.access_token_expire,
			user.admin,
			user.created,
			user.agreement,
			date_format(user.checked_forum,'%m/%d/%Y %H:%i:%s') as checked_forum,
			date_format(user.google_last_sync_on,'%m/%d/%Y %H:%i:%s') as google_last_sync_date,
			business.timezone,
			business.dst,
			business.name as business_name,
			user.unsubscribe,
			user.force_google_sync
			from user
			left join business on business.id = user.business_id
				and business.active = 1
			where user.active = 1
			<cfif arguments.email neq ''>
				and user.email = #$.database.string(arguments.email)#
			</cfif>
			<cfif arguments.password neq ''>
				and user.password = #$.database.string($.format.encrypt(arguments.password))#
			</cfif>
			<cfif arguments.userID neq ''>
				and user.id = #$.database.uuid(arguments.userID)#
			</cfif>
			<cfif arguments.order neq "">
				order by #arguments.order#
			</cfif>
		</cfquery>
		
		<cfreturn local.getUser />
		
	</cffunction>
	
	<!--- --->
	
	<cffunction name="getBusiness" access="public" output="true" returntype="query">
		<cfargument name="businessID" required="false" default="" />
		<cfargument name="userID" required="false" default="" />
				
		<cfset var local = {} />

		
		<cfquery name="local.getBusiness" datasource="#$.site.getVar('datasource')#">
			select 
			hex(business.id) as id,
			business.name,
			business.website,
			business.business_tags,
			business.description,
			hex(business.state_id) as state_id,
			business.timezone,
			business.dst,
			state.name as state_name,
			state.abbrev as state_abbrev,
			business.continent
			from business
			<cfif arguments.userID neq ''>
				inner join user on user.business_id = business.id
				and user.id = #$.database.uuid(arguments.userID)#
			</cfif>
			left join state on state.id = business.state_id
			where business.active = 1
			<cfif arguments.businessID neq ''>
				and business.id = #$.database.uuid(arguments.businessID)#
			</cfif>
			<cfif arguments.userID eq "" and arguments.businessID eq "">
				and 1 = 0	
			</cfif>
		</cfquery>
		
		<cfreturn local.getBusiness />
		
	</cffunction>
	
	<!--- --->
	
	<cffunction name="getUserGoogleCalendars" access="public" output="true" returntype="query">
		<cfargument name="userID" required="false" default="" />
				
		<cfset var local = {} />
		
		<cfquery name="local.getUserGoogleCalendars" datasource="#$.site.getVar('datasource')#">
			select 
			google_calendar_ids,
			google_calendar_id
			from user
			where user.id = #$.database.uuid(arguments.userID)#
		</cfquery>
		
		<cfreturn local.getUserGoogleCalendars />
		
	</cffunction>
	
	<!--- --->
	
	<cffunction name="getUserCount" access="public" output="true" returntype="query">

		<cfset var local = {} />
		
		<cfquery name="local.getUserCount" datasource="#$.site.getVar('datasource')#">
			select 
			count(user.id) as counts
			from user
			where active = 1
			and admin = 0
		</cfquery>
		
		<cfreturn local.getUserCount />
		
	</cffunction>
	
	<!--- --->
	
	<cffunction name="getUserData" access="public" output="true" returntype="query">
		<cfargument name="email" required="false" default="" />
		<cfargument name="password" required="false" default="" />
		<cfargument name="userID" required="false" default="" />
		<cfargument name="invalidateCache" required="false" default="true" /> <!--- for now, never cache this query --->
		<cfargument name="email_template_name" required="false" default="" />
		<cfargument name="last_request" required="false" default="" />
		<cfargument name="unsubscribe" required="false" default="" />
		<cfargument name="order" required="false" default="" />
		<cfargument name="limit" required="false" default="" />
		
		<cfset var local = {} />
		
		<cfquery name="local.getUserData" datasource="#$.site.getVar('datasource')#">
			select 
			hex(user.id) as id,
			user.first_name,
			user.middle_name,
			user.last_name,
			user.email,
			user.password,
			user.bio,
			user.event_categories,
			user.options,
			hex(user.business_id) as business_id,
			user.admin,
			user.created,
			user.agreement,
			date_format(user.checked_forum,'%m/%d/%Y %H:%i:%s') as checked_forum,
			date_format(user.google_last_sync_on,'%m/%d/%Y %H:%i:%s') as google_last_sync_date,
			business.timezone,
			business.dst,
			business.name as business_name,
			business.website,
			user.force_google_sync,
			date_format(user.last_request,'%m/%d/%Y %H:%i:%s') as last_request,
			ifnull(goal_count.counts,0) as goal_count,
			ifNull(event_count.counts,0) as event_count,
			ifNull(workweek_count.counts,0) as workweek_count,
			ifNull(important_count.counts,0) as important_count,
			ifNull(user_email_logs.counts,0) as email_count,
			user.unsubscribe
			from user
			left join business on business.id = user.business_id
				and business.active = 1
			left join (
				select
				count(goal.id) as counts,
				goal.user_id
				from goal
				inner join user on user.id = goal.user_id
				where goal.active = 1
				and goal.created >= date_add(user.created,INTERVAL 10 SECOND)
				group by goal.user_id
			) goal_count on goal_count.user_id = user.id
			left join (
				select
				count(event.id) as counts,
				event.user_id
				from event
				where event.active = 1
				group by event.user_id
			) event_count on event_count.user_id = user.id
			left join (
				select
				count(workweek_event.id) as counts,
				workweek_event.user_id
				from workweek_event
				inner join user on user.id = workweek_event.user_id
				and workweek_event.created >= date_add(user.created,INTERVAL 10 SECOND)
				where workweek_event.active = 1
				group by workweek_event.user_id
			) workweek_count on workweek_count.user_id = user.id
			left join (
				select
				count(important.id) as counts,
				important.user_id
				from important
				where important.active = 1
				and important.week = #week(now())#
				group by important.user_id
			) important_count on important_count.user_id = user.id
			left join (
				select
				count(user_email_log.id) as counts,
				user_email_log.user_id
				from user_email_log
				where user_email_log.active = 1
				<cfif arguments.email_template_name neq "">
					and user_email_log.template_name = #$.database.string(arguments.email_template_name)#
				</cfif>
				group by user_email_log.user_id
			) user_email_logs on user_email_logs.user_id = user.id
			where user.active = 1
			<cfif arguments.email neq ''>
				and user.email = #$.database.string(arguments.email)#
			</cfif>
			<cfif arguments.password neq ''>
				and user.password = #$.database.string($.format.encrypt(arguments.password))#
			</cfif>
			<cfif arguments.userID neq ''>
				and user.id = #$.database.uuid(arguments.userID)#
			</cfif>
			<cfif arguments.email_template_name neq "">
				and ifNull(user_email_logs.counts,0) = 0
			</cfif>
			<cfif arguments.last_request neq "">
				and (
					user.last_request < #$.database.datetime(arguments.last_request)#
					or user.last_request is null
				)
			</cfif>
			<cfif arguments.unsubscribe neq "">
				and user.unsubscribe = #$.database.string(arguments.unsubscribe)#
			</cfif>

			<cfif arguments.order neq "">
				order by #arguments.order#
			</cfif>
			<cfif arguments.limit neq "">
				limit #arguments.limit#
			</cfif>
		</cfquery>
		
		<cfreturn local.getUserData />
		
	</cffunction>
	
	<!--- --->
	
</cfcomponent>